package com.cyys.common.utils;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;



/**
 * 压缩包工具类
 * @author pc
 *
 */
public class ZipUtil {

	public static Log log = LogFactory.getLog(ZipUtil.class);
	//正则表达式 用于匹配属性的第一个字母
	private static final String REGEX = "[a-zA-Z]";
	private final static String excel2003L =".xls";    //2003- 版本的excel
	private final static String excel2007U =".xlsx";   //2007+ 版本的excel


	/**
	 * 创建压缩包
	 * @param filePath
	 * @param zipFilePath
	 */
	public static void CreateZipFile(String filePath, String zipFilePath) {
		FileOutputStream fos = null;
		ZipOutputStream zos = null;
		try {
			fos = new FileOutputStream(zipFilePath);
			zos = new ZipOutputStream(fos);
			writeZipFile(new File(filePath), zos, "");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				if (zos != null)
					zos.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
			try {
				if (fos != null)
					fos.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

	}

	/**
	 * 创建多个压缩包文件
	 * @param filePath
	 * @return
	 */
	public static String CreateZipMultiFile(String filePath[]) {
		FileOutputStream fos = null;
		ZipOutputStream zos = null;
		String zipFilePath = "";
		try {
			int length = filePath.length;
			if(length > 0){
				if(StringUtils.isBlank(zipFilePath)){
					zipFilePath = filePath[0].split(",")[1];
				}
				fos = new FileOutputStream(zipFilePath,true);
				zos = new ZipOutputStream(fos);
				for (int i = 0; i < length; i++) {
					writeZipFile(new File(filePath[i].split(",")[0]), zos, "");
				}
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				if (zos != null)
					zos.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
			try {
				if (fos != null)
					fos.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return zipFilePath;
	}

	/**
	 * 输出压缩包文件
	 * @param f
	 * @param zos
	 * @param hiberarchy
	 */
	public static void writeZipFile(File f, ZipOutputStream zos,
									String hiberarchy) {
		if (f.exists()) {
			if (f.isDirectory()) {
				hiberarchy += f.getName() + "/";
				File[] fif = f.listFiles();
				for (File file : fif) {
					writeZipFile(file, zos, hiberarchy);
				}
			} else {
				FileInputStream fis = null;
				try {
					fis = new FileInputStream(f);
					ZipEntry ze = new ZipEntry(f.getName());
					zos.putNextEntry(ze);
					byte[] b = new byte[1024];
					while (fis.read(b) != -1) {
						zos.write(b);
						b = new byte[1024];
					}
				} catch (FileNotFoundException e) {
					e.printStackTrace();
				} catch (IOException e) {
					e.printStackTrace();
				} finally {
					try {
						if (fis != null) {
							fis.close();
						}
					} catch (IOException e) {
						e.printStackTrace();
					}
				}

			}
		}

	}

	/**
	 * 生成Excel
	 * @param tabList 集合列表，也就是把list在装到list里面，分页数据集合
	 * @param filePath 生成的文件路径
	 * @param titleArr Excel的列名 new String[]{"用户名","密码","邮箱"}
	 * @param keyArr 读取集合中的字段 new String[]{"name","password","email"}
	 * @param
	 * @throws Exception
	 */
	public static void createExcels(List<Map<String, List<Map<String, Object>>>> tabList, String filePath, String[] titleArr, String[] keyArr)
			throws Exception {
		if(tabList!=null && tabList.size()>0){
			createFile(filePath);
			int i=1;
			for (Map<String, List<Map<String, Object>>> map : tabList) {
				// 导出excel
				List<Map<String, Object>> list = map.get("list");
				HSSFWorkbook wb = exportExcel(list, titleArr, keyArr);
				DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
				Date data=new Date();
				String date = df.format(data);
				if (wb != null) {
					FileOutputStream os = new FileOutputStream(new File(filePath+date+i+".xls"));
					wb.write(os);
					os.flush();
					os.close();
					os = null;
					wb = null;
				}
				i++;
			}
		}
	}

	/**
	 * 生成Excel的压缩包
	 * @param tabList  集合列表，也就是把list在装到list里面，分页数据集合,一个集合生成一个Excel
	 * @param path 临时文件生成路径，用户传入服务器地址，然后通过默认规范进行拼接
	 * @param zipFileName 压缩包文件名
	 * @param otherPath 临时文件路径，优先级高于path。当otherPath不为空时，则临时文件存放目录以此为准不会作其他拼接
	 * @param titleArr Excel的列名 new String[]{"用户名","密码","邮箱"}
	 * @param keyArr 读取集合中的字段 new String[]{"name","password","email"}
	 * @return
	 * @throws Exception
	 */
	public static String createZip(List<Map<String, List<Map<String, Object>>>> tabList, String path, String zipFileName,
								   String otherPath, String[] titleArr, String[] keyArr) throws Exception {
		if (StringUtils.isBlank(otherPath)) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
			Date newDate = new Date();
			String newDateStr = sdf.format(newDate);
			path = path + "/zips/" + newDateStr + "/" + newDate.getTime();
		} else {
			path = otherPath;
		}

		String filePath = path + "/excel/";// excel文件路径
		String zipFilePath = path + "/zip/";// zip文件路径
		createFile(filePath);
		createFile(zipFilePath);

		if (StringUtils.isBlank(zipFileName)) {
			zipFilePath = zipFilePath + "project.zip";
		} else {
//			DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
//			Date data=new Date();
//			String date = df.format(data);
			zipFilePath = zipFilePath +zipFileName+".zip";
		}
		createExcels(tabList, filePath, titleArr, keyArr);
		CreateZipFile(filePath, zipFilePath);
		return zipFilePath;
	}


	/**
	 * 生成Excel的压缩包
	 * @param tabList  集合列表，也就是把list在装到list里面，分页数据集合,一个集合生成一个Excel
	 * @param path 临时文件生成路径，用户传入服务器地址，然后通过默认规范进行拼接
	 * @param zipFileName 压缩包文件名
	 * @param
	 * @param otherPath 临时文件路径，优先级高于path。当otherPath不为空时，则临时文件存放目录以此为准不会作其他拼接
	 * @param titleArr Excel的列名 new String[]{"用户名","密码","邮箱"}
	 * @param keyArr 读取集合中的字段 new String[]{"name","password","email"}
	 * @return
	 * @throws Exception
	 */
	public static String createExcel(List<Map<String, List<Map<String, Object>>>> tabList, String path, String zipFileName,
									 String otherPath, String[] titleArr, String[] keyArr) throws Exception {
		if (StringUtils.isBlank(otherPath)) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
			Date newDate = new Date();
			String newDateStr = sdf.format(newDate);
			path = path + "/zips/" + newDateStr + "/" + newDate.getTime();
		} else {
			path = otherPath;
		}

		String filePath = path + "/excel/";// excel文件路径
		String zipFilePath = path + "/zip/";// zip文件路径
		createFile(filePath);
		createFile(zipFilePath);

		if (StringUtils.isBlank(zipFileName)) {
			zipFilePath = zipFilePath + "project.zip";
		} else {
			DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
			Date data=new Date();
			String date = df.format(data);
			zipFilePath = zipFilePath + date+zipFileName+".zip";
		}
		createExcels(tabList, filePath, titleArr, keyArr);
		return filePath+","+zipFilePath;
	}

	/**
	 * excel生成格式
	 *
	 * @param list
	 *            数据源
	 * @param row1
	 *            头部列说明 new String[]{"用户名","密码","邮箱"}
	 * @param rows
	 *            列字段内容 new String[]{"name","password","email"}
	 *            两者相互对应,上面的来自用户对字段的说明，下者来自数据库查询出的字段
	 * @return
	 */

	public static HSSFWorkbook exportInnerUserExcel(
			List<Map<String, Object>> list, String[] row1, String[] rows) {
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			HSSFSheet sheet = null;
			sheet = wb.createSheet("sheet1");
			HSSFRow topRow = sheet.createRow(0);
			for (int i = 0; i < row1.length; i++) {
				setCellGBKValue(topRow.createCell((short) i), row1[i]);
			}

			HSSFRow row = null;
			int length = list.size();
			Map<String, Object> map;
			int j = 1;
			for (int i = 0; i < length; i++) {
				row = sheet.createRow(j);
				map = list.get(i);
				for (int k = 0; k < rows.length; k++) {
					setCellGBKValue(row.createCell((short) k), map.get(rows[k])
							+ "");
				}
				map = null;
				row = null;
				j++;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return wb;
	}

	/**
	 * excel生成格式
	 *
	 * @param list
	 *            数据源
	 * @param titleArr
	 *            头部列说明 new String[]{"用户名","密码","邮箱"}
	 * @param keyArr
	 *            列字段内容 new String[]{"name","password","email"}
	 *            两者相互对应,上面的来自用户对字段的说明，下者来自数据库查询出的字段
	 * @return
	 */

	public static HSSFWorkbook exportExcel(List<Map<String, Object>> list,
										   String[] titleArr, String[] keyArr) {
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			HSSFSheet sheet = null;
			sheet = wb.createSheet("sheet1");
			//设置表头的说明
			HSSFRow topRow = sheet.createRow(0);
			//设置样式
			HSSFFont font = wb.createFont();
			HSSFCellStyle cellStyle = wb.createCellStyle();
			font.setBold(true);
			font.setFontName("微软雅黑");
			cellStyle.setFont(font);
			cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
			cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			//设置列宽
			for(int i = 0; i < titleArr.length; i ++){
				sheet.setColumnWidth((short)i, (short)7000);
				HSSFCell cell = topRow.createCell((short) i);
				cell.setCellStyle(cellStyle);
				setCellGBKValue(cell, titleArr[i].trim());
			}

			HSSFRow row = null;
			int length = list.size();
			Map<String, Object> map;
			int j = 1;
			String obj = "";
			for (int i = 0; i < length; i++) {
				row = sheet.createRow(j);
				map = list.get(i);
				for (int k = 0; k < keyArr.length; k++) {
					if(!keyArr[k].equals("完成")){
						obj =  map.get(keyArr[k].trim())==null?"":map.get(keyArr[k].trim()).toString();

					}else{
						obj="完成";
					}
					setCellGBKValue(row.createCell((short) k), obj);
				}
				map = null;
				row = null;
				j++;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return wb;
	}

	/**
	 * 设置excel编码
	 *
	 * @param cell
	 * @param value
	 */
	public static void setCellGBKValue(HSSFCell cell, String value) {
		//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellType(CellType.STRING);
		cell.setCellValue(value);
	}

	/**
	 * 设置excel公式
	 *
	 * @param
	 * @param value
	 */
	public static void setCellFormatValue(HSSFRow row, short s, String value) {
		HSSFCell cell = row.createCell(s);
		cell.setCellType(CellType.FORMULA);
		cell.setCellFormula(value);

	}

	/**
	 *
	 * @param num
	 * @return
	 */
	public static String getFormula(int num) {
		String str = "";
		int a = num / 26;
		int b = num % 26;
		if (b == 0) {
			a--;
		}
		if (a > 0) {
			str = ((char) (64 + a)) + "";
		}
		if (b > 0) {
			str = str + ((char) (64 + b));
		}
		if (b == 0) {
			str = str + ((char) (64 + 26));
		}
		return str;
	}

//	public static void main(String[] args) {
//		String url="c://luopeng.doc,c://luopeng1.doc";
//		String url1="c://luopeng.zip";
//		CreateZipFile(url,url1);
//	//	System.out.println(getFormula(12));
//	}

	/**
	 * 创建文件夹
	 *
	 * @param filePath
	 */
	public static void createFile(String filePath) {
		File file = new File(filePath);
		if (!file.exists()) {
			file.mkdirs();
		}
	}

	public static char getChar(int i) {
		return (char) (64 + i);
	}

	/**
	 * 功能: Excel数据导入到数据库
	 * 参数: originUrl[Excel表的所在路径]
	 * 参数: startRow[从第几行开始]
	 * 参数: endRow[到第几行结束(0表示所有行;正数表示到第几行结束;负数表示到倒数第几行结束)]
	 * 参数: clazz[要返回的对象集合的类型]
	 */
	public static List<?> importExcel(InputStream originUrl,int startRow,int endRow,Class<?> clazz) throws IOException {
		//是否打印提示信息
		boolean showInfo=true;
		return doImportExcel(originUrl,startRow,endRow,showInfo,clazz);
	}

	/**
	 * 功能:真正实现导入
	 */
	private static List<Object> doImportExcel(InputStream originUrl,int startRow,int endRow,boolean showInfo,Class<?> clazz) throws IOException {
		XSSFWorkbook wb = null;
		List<Row> rowList = new ArrayList<Row>();
		try {
			// 去读Excel
			wb = new XSSFWorkbook(originUrl);
			Sheet sheet = wb.getSheetAt(0);
			// 获取最后行号
			int lastRowNum = sheet.getLastRowNum();
			if (lastRowNum > 0) { // 如果>0，表示有数据
				out("\n开始读取名为【" + sheet.getSheetName() + "】的内容：",showInfo);
			}
			Row row = null;
			// 循环读取
			for (int i = startRow; i <= lastRowNum + endRow; i++) {
				row = sheet.getRow(i);
				if (row != null) {
					rowList.add(row);
					out("第" + (i + 1) + "行：",showInfo,false);
					// 获取每一单元格的值
					for (int j = 0; j < row.getLastCellNum(); j++) {
						String value = getCellValue(row.getCell(j));
						if (!value.equals("")) {
							out(value + " | ",showInfo,false);
						}
					}
					out("",showInfo);
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		} finally{
			//wb.cloneSheet(endRow);
		}
		return returnObjectList(rowList,clazz);
	}

	/**
	 * 功能:获取单元格的值
	 */
	private static String getCellValue(Cell cell) {
		Object result = "";
		if (cell != null) {
			if(cell.getCellType() == CellType.NUMERIC){
				if(String.valueOf(cell.getNumericCellValue()).indexOf("E")==-1){
					return String.valueOf(cell.getNumericCellValue());
				}else {
					return new DecimalFormat("#").format(cell.getNumericCellValue());
				}
			}
			switch (cell.getCellType()) {
				case STRING:
					result = cell.getStringCellValue();
					break;
				case NUMERIC:
					result = cell.getNumericCellValue();
					break;
				case BOOLEAN:
					result = cell.getBooleanCellValue();
					break;
				case FORMULA:
					result = cell.getCellFormula();
					break;
				case ERROR:
					result = cell.getErrorCellValue();
					break;
				case BLANK:
					break;
				default:
					break;
			}
		}
		return result.toString();
	}

	/**
	 * 功能:返回指定的对象集合
	 */
	private static List<Object> returnObjectList(List<Row> rowList,Class<?> clazz) {
		List<Object> objectList=null;
		Object obj=null;
		String attribute=null;
		String value=null;
		int j=0;
		try {
			objectList=new ArrayList<Object>();
			Field[] declaredFields = clazz.getDeclaredFields();
			for (Row row : rowList) {
				j=0;
				obj = clazz.newInstance();
				for (Field field : declaredFields) {
					attribute=field.getName().toString();
					value = getCellValue(row.getCell(j));
					setAttrributeValue(obj,attribute,value);
					j++;
				}
				objectList.add(obj);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return objectList;
	}

	/**
	 * 功能:给指定对象的指定属性赋值
	 */
	private static void setAttrributeValue(Object obj,String attribute,String value) {
		//得到该属性的set方法名
		String method_name = convertToMethodName(attribute,obj.getClass(),true);
		Method[] methods = obj.getClass().getMethods();
		for (Method method : methods) {
			/**
			 * 因为这里只是调用bean中属性的set方法，属性名称不能重复
			 * 所以set方法也不会重复，所以就直接用方法名称去锁定一个方法
			 * （注：在java中，锁定一个方法的条件是方法名及参数）
			 */
			if(method.getName().equals(method_name))
			{
				Class<?>[] parameterC = method.getParameterTypes();
				try {
					/**如果是(整型,浮点型,布尔型,字节型,时间类型),
					 * 按照各自的规则把value值转换成各自的类型
					 * 否则一律按类型强制转换(比如:String类型)
					 */
					if(parameterC[0] == int.class || parameterC[0]==Integer.class)
					{
						value = value.substring(0, value.lastIndexOf("."));
						method.invoke(obj,Integer.valueOf(value));
						break;
					}else if(parameterC[0] == float.class || parameterC[0]==Float.class){
						method.invoke(obj, Float.valueOf(value));
						break;
					}else if(parameterC[0] == double.class || parameterC[0]==Double.class)
					{
						method.invoke(obj, Double.valueOf(value));
						break;
					}else if(parameterC[0] == byte.class || parameterC[0]==Byte.class)
					{
						method.invoke(obj, Byte.valueOf(value));
						break;
					}else if(parameterC[0] == boolean.class|| parameterC[0]==Boolean.class)
					{
						method.invoke(obj, Boolean.valueOf(value));
						break;
					}else if(parameterC[0] == Date.class)
					{
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
						Date date=null;
						try {
							date=sdf.parse(value);
						} catch (Exception e) {
							e.printStackTrace();
						}
						method.invoke(obj,date);
						break;
					}else
					{
						method.invoke(obj,parameterC[0].cast(value));
						break;
					}
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					e.printStackTrace();
				} catch (SecurityException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * 功能:根据属性生成对应的set/get方法
	 */
	private static String convertToMethodName(String attribute,Class<?> objClass,boolean isSet) {
		/** 通过正则表达式来匹配第一个字符 **/
		Pattern p = Pattern.compile(REGEX);
		Matcher m = p.matcher(attribute);
		StringBuilder sb = new StringBuilder();
		/** 如果是set方法名称 **/
		if(isSet)
		{
			sb.append("set");
		}else{
			/** get方法名称 **/
			try {
				Field attributeField = objClass.getDeclaredField(attribute);
				/** 如果类型为boolean **/
				if(attributeField.getType() == boolean.class||attributeField.getType() == Boolean.class)
				{
					sb.append("is");
				}else
				{
					sb.append("get");
				}
			} catch (SecurityException e) {
				e.printStackTrace();
			} catch (NoSuchFieldException e) {
				e.printStackTrace();
			}
		}
		/** 针对以下划线开头的属性 **/
		if(attribute.charAt(0)!='_' && m.find())
		{
			sb.append(m.replaceFirst(m.group().toUpperCase()));
		}else{
			sb.append(attribute);
		}
		return sb.toString();
	}

	/**
	 * 功能:输出提示信息(普通信息打印)
	 */
	private static void out(String info, boolean showInfo) {
		if (showInfo) {
			System.out.print(info + (showInfo ? "\n" : ""));
		}
	}

	/**
	 * 功能:输出提示信息(同一行的不同单元格信息打印)
	 */
	private static void out(String info, boolean showInfo, boolean nextLine) {
		if (showInfo) {
			if(nextLine)
			{
				System.out.print(info + (showInfo ? "\n" : ""));
			}else
			{
				System.out.print( info );
			}
		}
	}


}
